alter PROCEDURE [dbo].[getOrderdetailreport] @BatchNumber varchar(200),
                                              @begingmt_create DATETIME,
                                              @endgmt_create DATETIME,
    --部门
                                              @BoStatus NVARCHAR(50),
    --Cust No
                                              @TypeMe NVARCHAR(50),
    --产线id
                                              @ProductionLineId NVARCHAR(50),
    --客户名称
                                              @khmc NVARCHAR(50),
    --客户代码
                                              @khdm NVARCHAR(50),
    --是否急单
                                              @sfjd tinyint,
                                              @ITEM_CODE NVARCHAR(100),
                                              @PlannerCode NVARCHAR(100),

    --批量工单号
                                              @BatchNumberList NVARCHAR(2000),
    -- 批量job
                                              @JobList NVARCHAR(2000),
    --批量order
                                              @OrderList NVARCHAR(2000),
                                              @PageIndex INT,
                                              @PageSize INT,
                                              @TotalCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- 计算分页的起始位置
    DECLARE @Offset INT = (@PageIndex - 1) * @PageSize;
--------------------------------------------------------------------------------------------
    select b.batch_created_date                              batchCreatedDate,
           a.gmt_create                                      MES订单创建时间,
           a.batch_number                                    工单号,
           a.qty                                             数量,
           a.customer_item_name                              客户产品名称,
           a.item_code                                       产品名称,
           c.[order]                                         Order_,
           c.line                                            Line_,
           c.job                                             Job_,
           c.job_qty                                         Job_Qty,
           c.ship_set                                        Ship_Set,
           a.customer_name                                   客户名称,
           a.customer_code                                   客户代码,
           a.item_category                                   产品类别,
           f.open_paper_info                                 开纸信息,
           f.paper_open                                      纸张开数,
           a.production_line                                 产线,
           a.team                                            Team,
           a.unit                                            单位,
           g.labeling_ups                                    Labeling_UPS_RFID,
           a.number_of_layouts                               排版个数,
           a.lot_qty                                         LOT个数,
           w.material_total                                  领料合计_主料,
           a.process_line_desc                               工序汇总,
           a.billing_officer                                 开单人员,
           a.standard_shipping_date                          标准出货日期,
           a.customer_requires_shipping_date                 客户要求出货日期,
           a.ppc_contact                                     PPC负责人,
           a.setup_pe                                        Setup_PE,
           a.develop_pe                                      Develop_PE,
           aa.ymss                                           印色,
           e.color_two                                       颜色2,
           x.material_code                                   物料编码,
           y.material_code                                   芯片物料编码,
           a.current_site                                    当前站点,
           j.scan_time                                       站点时间,
           datediff(minute, j.scan_time, getdate())          停留时长,
           a.printer_type                                    PrintType,
           k.current_location                                CurrentLocation,
           case when a.hold_flag = 1 then 'Y' else 'N' end   Hold,
           a.ppc_recurrence                                  PPC复期,
           case when a.urgent = 1 then 'Y' else 'N' end      是否急单,
           (SELECT TOP 1 m2.urgent_reason
            FROM mes_coe m2
            WHERE m2.bo_id = a.id
            ORDER BY CASE m2.urgent_grade
                         WHEN 'Grade S' THEN 1
                         WHEN 'Grade A' THEN 2
                         WHEN 'Grade B' THEN 3
                         WHEN 'Grade C' THEN 4
                         WHEN 'Grade D' THEN 5
                         ELSE 6
                         END)                                急单原因,
           (SELECT TOP 1 m2.urgent_grade
            FROM mes_coe m2
            WHERE m2.bo_id = a.id
            ORDER BY CASE m2.urgent_grade
                         WHEN 'Grade S' THEN 1
                         WHEN 'Grade A' THEN 2
                         WHEN 'Grade B' THEN 3
                         WHEN 'Grade C' THEN 4
                         WHEN 'Grade D' THEN 5
                         ELSE 6
                         END)                                Urgent_Grade,
           datediff(day, a.mes_order_create_time, getdate()) Pending_Days,
           o.sub_brand                                       Sub_brand,
           a.so_order_type                                   Order_Type,
           a.wh_org                                          WH_Org,
           p.goes_order_and_line                             GOES_Order_and_Line,
           a.customer_name                                   客户名称,
           p.customer_po_number                              PO_Number,
           o.production_methods                              Production_Methods,
           a.planner_code                                    Planner_code,
           p.bill_to_customer                                Bill_To_Customer,
           p.ship_to_customer                                Ship_To_Customer,
           p.order_source                                    Order_Source,
           p.customer_service_rep                            CSR,
           p.packing_instructions                            Packing_Instruction,
           p.manufacturing_notes                             Manufacturing_Notes,
           bostatus.name                                     订单状态,
           q.classification                                  Classification,
           z.gmt_create                                      完成日期,
           a.typesetting_method                              Typesetting_Method,
           a.Priority                                        Priority,
           a.target_date                                     TargetDate,
           mes_type.name                                     订单类型,
           a.sku_qty                                         SKU个数,
           a.gross_discharge_qty                             总放数,
           a.paper_used_num_total                            净需求数,
           pczt.name                                         BO排程状态,
           coe.crd                                           COE_CRD,
           a.ENG                                             ENG,
           a.plate_serial_number                             搭版流水号,
           -- 新增字段
           CONCAT(a.item_length, '*', a.item_width)          产品尺寸,
           coe.upload_date                                   COE提交时间,
           a.cancel_reason                                   cancel原因,
           a.cancel_time                                     cancel时间,
           a.cancel_user_name                                cancel账号,
           a.pages                                           页数,
           a.neutralization_conversion_factor                中离转换系数,
           a.loss_conversion_coefficient                     放损转换系数,
--            (CASE
--                 WHEN EXISTS (SELECT 1 FROM mes_bo_combo_result WHERE batch_number = a.batch_number)
--                     THEN 'Y'
--                 ELSE 'N'
--                END)              as                          订单是否combo
           CASE
               WHEN EXISTS (SELECT TOP 1 1
                            FROM dbo.mes_bo_combo_relationship r
                                     CROSS APPLY (VALUES (master_item_code),
                                                         (deputy_item_code1),
                                                         (deputy_item_code2),
                                                         (deputy_item_code3),
                                                         (deputy_item_code4),
                                                         (deputy_item_code5),
                                                         (deputy_item_code6),
                                                         (deputy_item_code7),
                                                         (deputy_item_code8),
                                                         (deputy_item_code9),
                                                         (deputy_item_code10),
                                                         (deputy_item_code11),
                                                         (deputy_item_code12),
                                                         (deputy_item_code13),
                                                         (deputy_item_code14),
                                                         (deputy_item_code15),
                                                         (deputy_item_code16),
                                                         (deputy_item_code17),
                                                         (deputy_item_code18),
                                                         (deputy_item_code19),
                                                         (deputy_item_code20),
                                                         (deputy_item_code21),
                                                         (deputy_item_code22),
                                                         (deputy_item_code23),
                                                         (deputy_item_code24),
                                                         (deputy_item_code25),
                                                         (deputy_item_code26),
                                                         (deputy_item_code27),
                                                         (deputy_item_code28),
                                                         (deputy_item_code29),
                                                         (deputy_item_code30)) AS unpvt(item_code)
                            WHERE unpvt.item_code = a.item_code)
                   THEN 'Y'
               ELSE 'N'
               END          as                               订单是否combo,
           case
               when a.printing_status = 1
                   then 'Y'
               else 'N' end as                               打印状态

    FROM mes_batch_order a

             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_or_batch b
                          WHERE a.batch_number = b.batch_number
                          ORDER BY b.id) b
             LEFT JOIN mes_bo_dj c ON a.id = c.bo_id
             LEFT JOIN mes_coe coe on coe.bo_dj_id = c.id

        -- 物料工序
             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_bo_process wlgx
                          WHERE wlgx.bo_id = a.id
                            and wlgx.group_type = 'CUTTING') wlgx
             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_bo_process wlgx
                          WHERE wlgx.bo_id = a.id
                            and wlgx.group_type = 'print') prgx
             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_bo_process_detail e
                          WHERE e.bo_process_id = prgx.id
                          ORDER BY e.id) e
             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_bo_process_material f
                          WHERE f.bo_process_id = wlgx.id
                          ORDER BY f.id) f
             OUTER APPLY (SELECT TOP 1 *
                          FROM sys_stepping_template g
                          WHERE g.id = a.stepping_id
                          ORDER BY g.id) g
             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_ps_after h
                          WHERE h.bo_id = a.id
                          ORDER BY h.id) h


             OUTER APPLY (select top 1 *
                          from mes_site_scan j
                          where j.bo_id = a.id
                          order by j.gmt_create desc) j
             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_me_move_location_record k
                          WHERE k.bo_id = a.id
                          ORDER BY k.id) k
             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_bo_hold l
                          WHERE l.bo_id = a.id
                          ORDER BY l.id) l

             OUTER APPLY (SELECT TOP 1 *
                          FROM sys_item_master o
                          WHERE o.item_code = a.item_code
                          ORDER BY o.id) o
             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_or_sol p
                          WHERE p.job COLLATE Chinese_PRC_CI_AS = c.job COLLATE Chinese_PRC_CI_AS
                          ORDER BY p.id) p
             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_or_subbatch q
                          WHERE q.batch_number = a.batch_number
                          ORDER BY q.id) q
             OUTER APPLY (SELECT TOP 1 a.*
                          from mes_bo_process_material a
                          WHERE a.bo_process_id = wlgx.id) x
             OUTER APPLY (SELECT TOP 1 aa.material_code
                          from mes_bo_process_material aa
                                   left join sys_material_type b on aa.material_type = b.id
                          WHERE aa.bo_id = a.id
                            and b.name like N'%芯片%') y
             outer apply (select top 1 d.gmt_create
                          from mes_batch_order z
                                   left join mes_site_template b on z.production_line_id = b.production_line_id
                                   left join mes_site c on b.id = c.site_template_id and c.site_code = 999
                                   left join mes_site_scan d on c.id = d.scan_site_id and bo_id = z.id
                          where a.batch_number = z.batch_number) z
             outer apply (select top 1 *
                          from mes_bo_process_detail a
                          where a.bo_process_id = wlgx.id) w
             left join (SELECT a.id,
                               a.batch_number,
                               COALESCE(
                                       NULLIF(
                                               CONCAT(
                                                       COUNT(CASE WHEN c.print_surface = '正面' THEN 1 END),
                                                       '+',
                                                       COUNT(CASE WHEN c.print_surface = '反面' THEN 1 END)
                                               ),
                                               '0+0'
                                       ),
                                       '0+0'
                               ) ymss
                        FROM mes_batch_order a
                                 LEFT JOIN mes_bo_process b ON a.id = b.bo_id AND b.group_type = 'print'
                                 LEFT JOIN mes_bo_process_detail c ON b.id = c.bo_process_id
                            AND (c.color_one NOT LIKE '%VAR%' OR c.color_one IS NULL)
                            AND (c.color_two NOT LIKE '%VAR%' OR c.color_two IS NULL)
                        WHERE a.batch_number = a.batch_number
                        GROUP BY a.id, a.batch_number) aa on aa.id = a.id

             left join sys_dict bostatus on a.status = bostatus.value and bostatus.type = 'ORDER_STATUS'
             left join sys_dict mes_type on a.mes_order_type = mes_type.value and mes_type.type = 'MES_ORDER_TYPE'
             left join sys_dict pczt on a.scheduling_status = pczt.value and pczt.type = 'BO_PS_STATUS'


------------------------------------------------------------------------------------------------------


    WHERE (@BatchNumber IS NULL OR a.batch_number = @BatchNumber)
      AND (@BoStatus IS NULL OR a.status in (SELECT value FROM STRING_SPLIT(@BoStatus, ',')))
      AND (@TypeMe IS NULL OR a.typesetting_method in (SELECT value FROM STRING_SPLIT(@TypeMe, ',')))
      AND (@ProductionLineId IS NULL OR a.production_line_id = @ProductionLineId)
      AND (@begingmt_create IS NULL OR a.gmt_create >= @begingmt_create)
      AND (@endgmt_create IS NULL OR a.gmt_create <= @endgmt_create)
      AND (@khmc IS NULL OR a.customer_name like '%' + @khmc + '%')
      AND (@khdm IS NULL OR a.customer_code like '%' + @khdm + '%')
      AND (@sfjd IS NULL OR a.urgent = @sfjd)
      AND (@ITEM_CODE IS NULL OR a.item_code like '%' + @ITEM_CODE + '%')
      AND (@PlannerCode IS NULL OR a.planner_code like '%' + @PlannerCode + '%')
      AND (@BatchNumberList IS NULL OR
           EXISTS (SELECT 1 FROM STRING_SPLIT(@BatchNumberList, ' ') WHERE a.batch_number LIKE '%' + TRIM(value) + '%'))
      AND (@JobList IS NULL OR c.job in (SELECT value FROM STRING_SPLIT(@JobList, ' ')))
      AND (@OrderList IS NULL OR c.[order] in (SELECT value FROM STRING_SPLIT(@OrderList, ' ')))


    ORDER BY a.gmt_create -- 根据需要选择排序字段
    OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;

    SELECT @TotalCount = COUNT(1)

    ---------------------------------------------------------------------

    FROM mes_batch_order a

             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_or_batch b
                          WHERE a.batch_number = b.batch_number
                          ORDER BY b.id) b
             LEFT JOIN mes_bo_dj c ON a.id = c.bo_id
             LEFT JOIN mes_coe coe on coe.bo_dj_id = c.id

        -- 物料工序
             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_bo_process wlgx
                          WHERE wlgx.bo_id = a.id
                            and wlgx.group_type = 'CUTTING') wlgx
             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_bo_process wlgx
                          WHERE wlgx.bo_id = a.id
                            and wlgx.group_type = 'print') prgx
             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_bo_process_detail e
                          WHERE e.bo_process_id = prgx.id
                          ORDER BY e.id) e
             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_bo_process_material f
                          WHERE f.bo_process_id = wlgx.id
                          ORDER BY f.id) f
             OUTER APPLY (SELECT TOP 1 *
                          FROM sys_stepping_template g
                          WHERE g.id = a.stepping_id
                          ORDER BY g.id) g
             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_ps_after h
                          WHERE h.bo_id = a.id
                          ORDER BY h.id) h


             OUTER APPLY (select top 1 *
                          from mes_site_scan j
                          where j.bo_id = a.id
                          order by j.gmt_create desc) j
             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_me_move_location_record k
                          WHERE k.bo_id = a.id
                          ORDER BY k.id) k
             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_bo_hold l
                          WHERE l.bo_id = a.id
                          ORDER BY l.id) l

             OUTER APPLY (SELECT TOP 1 *
                          FROM sys_item_master o
                          WHERE o.item_code = a.item_code
                          ORDER BY o.id) o
             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_or_sol p
                          WHERE p.job COLLATE Chinese_PRC_CI_AS = c.job COLLATE Chinese_PRC_CI_AS
                          ORDER BY p.id) p
             OUTER APPLY (SELECT TOP 1 *
                          FROM mes_or_subbatch q
                          WHERE q.batch_number = a.batch_number
                          ORDER BY q.id) q
             OUTER APPLY (SELECT TOP 1 a.*
                          from mes_bo_process_material a
                          WHERE a.bo_process_id = wlgx.id) x
             OUTER APPLY (SELECT TOP 1 aa.material_code
                          from mes_bo_process_material aa
                                   left join sys_material_type b on aa.material_type = b.id
                          WHERE aa.bo_id = a.id
                            and b.name like N'%芯片%') y
             outer apply (select top 1 d.gmt_create
                          from mes_batch_order z
                                   left join mes_site_template b on z.production_line_id = b.production_line_id
                                   left join mes_site c on b.id = c.site_template_id and c.site_code = 999
                                   left join mes_site_scan d on c.id = d.scan_site_id and bo_id = z.id
                          where a.batch_number = z.batch_number) z
             outer apply (select top 1 *
                          from mes_bo_process_detail a
                          where a.bo_process_id = wlgx.id) w
             left join (SELECT a.id,
                               a.batch_number,
                               COALESCE(
                                       NULLIF(
                                               CONCAT(
                                                       COUNT(CASE WHEN c.print_surface = '正面' THEN 1 END),
                                                       '+',
                                                       COUNT(CASE WHEN c.print_surface = '反面' THEN 1 END)
                                               ),
                                               '0+0'
                                       ),
                                       '0+0'
                               ) ymss
                        FROM mes_batch_order a
                                 LEFT JOIN mes_bo_process b ON a.id = b.bo_id AND b.group_type = 'print'
                                 LEFT JOIN mes_bo_process_detail c ON b.id = c.bo_process_id
                            AND (c.color_one NOT LIKE '%VAR%' OR c.color_one IS NULL)
                            AND (c.color_two NOT LIKE '%VAR%' OR c.color_two IS NULL)
                        WHERE a.batch_number = a.batch_number
                        GROUP BY a.id, a.batch_number) aa on aa.id = a.id

             left join sys_dict bostatus on a.status = bostatus.value and bostatus.type = 'ORDER_STATUS'
             left join sys_dict mes_type on a.mes_order_type = mes_type.value and mes_type.type = 'MES_ORDER_TYPE'
             left join sys_dict pczt on a.scheduling_status = pczt.value and pczt.type = 'BO_PS_STATUS'


    WHERE (@BatchNumber IS NULL OR a.batch_number = @BatchNumber)
      AND (@BoStatus IS NULL OR a.status in (SELECT value FROM STRING_SPLIT(@BoStatus, ',')))
      AND (@TypeMe IS NULL OR a.typesetting_method in (SELECT value FROM STRING_SPLIT(@TypeMe, ',')))
      AND (@ProductionLineId IS NULL OR a.production_line_id = @ProductionLineId)
      AND (@begingmt_create IS NULL OR a.gmt_create >= @begingmt_create)
      AND (@endgmt_create IS NULL OR a.gmt_create <= @endgmt_create)
      AND (@khmc IS NULL OR a.customer_name like '%' + @khmc + '%')
      AND (@khdm IS NULL OR a.customer_code like '%' + @khdm + '%')
      AND (@sfjd IS NULL OR a.urgent = @sfjd)
      AND (@ITEM_CODE IS NULL OR a.item_code like '%' + @ITEM_CODE + '%')
      AND (@PlannerCode IS NULL OR a.planner_code like '%' + @PlannerCode + '%')
      AND (@BatchNumberList IS NULL OR a.batch_number in (SELECT value FROM STRING_SPLIT(@BatchNumberList, ' ')))
      AND (@JobList IS NULL OR c.job in (SELECT value FROM STRING_SPLIT(@JobList, ' ')))
      AND (@OrderList IS NULL OR c.[order] in (SELECT value FROM STRING_SPLIT(@OrderList, ' ')))

END